package Database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import Model.Appointment;
import Model.Approval;
import Model.StaffAppointment;

public class StaffAppointmentDB {
	private int noOfRecords;

	public static int numberOfAppointments(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) FROM Staffappointment WHERE UA_LoginID = ?";

		int number = 0;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				number = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return number;
	}
	
	public static int numberApproved(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) FROM Staffappointment WHERE UA_LoginID = ? AND UA_status = 2;";

		int number = 0;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				number = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return number;
	}

	public static int numberPending() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) FROM qmsdb.staffappointment WHERE UA_Status = -1;";

		int number = 0;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				number = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return number;
	}

	public static int numberPendingMR() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) FROM qmsdb.staffappointment WHERE UA_Status = 4;";

		int number = 0;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				number = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return number;
	}

	public static void createAppointment(StaffAppointment sa) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "INSERT INTO staffappointment(UA_LoginID, UA_AppointmentID, UA_EffectiveDate, UA_ApptGrpID, UA_MD5, UA_CreatedBy) VALUES (?, ?, ?, ?, ?, ?)";

		try {
			stmt = con.prepareStatement(select);
			System.out.println(select);
			stmt.setString(1, sa.getLoginID());
			stmt.setString(2, sa.getAppointmentID());
			stmt.setString(3, sa.getEffectiveDate());
			stmt.setString(4, sa.getApptGrpID());
			stmt.setString(5, sa.getMd5());
			stmt.setString(6, sa.getCreatedBy());

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static boolean checkDuplicateAppointmentEntry(StaffAppointment sa) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "Select * FROM staffappointment WHERE UA_LoginID =? and UA_AppointmentID = ?";
		boolean result = false;
		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, sa.getLoginID());
			stmt.setString(2, sa.getAppointmentID());
			rs = stmt.executeQuery();
			while (rs.next()) {
				result = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return result;
	}

	public static void rollback(String md5) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "DELETE FROM staffappointment WHERE UA_MD5 = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, md5);

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}
	// From here -code for approval


	public static ArrayList<Approval> getAllAppointments(String roleType) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, staffappointment.UA_diploma, staffappointment.UA_Year, ua_md5, staffappointment.UA_AppointmentID, staffappointment.UA_CreatedBy FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status < 1";
		
		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;
		//add appt diploma and year
		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, roleType);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setDiploma(rs.getString("UA_Diploma"));
				System.out.println("Diploma : " + appt.getDiploma());
				appt.setYear(rs.getString("UA_Year"));
				appt.setMd5(rs.getString("ua_md5"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));
				appt.setCreatedBy(rs.getString("UA_CreatedBy"));
				
				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		
		return apptArr;
	}

	// get all approved appointment
	public static ArrayList<Approval> getAllApprovedAppointments(String roleType) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		//add staffappointment.UA_diploma, staffappointment.UA_Year
		String select = "SELECT staffappointment.UA_AppointmentID, staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, staffappointment.UA_diploma, staffappointment.UA_Year, ua_md5, ua_approvedby, appointment.appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status = 2";
// joe  String select = "SELECT staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, staffappointment.UA_AppointmentID FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status < 1";
		
		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;
		// add appt.setDiploma(rs.getString("UA_Diploma")); System.out.println("Diploma : " + appt.getDiploma()); appt.setYear(rs.getString("UA_Year"));

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, roleType);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setDiploma(rs.getString("UA_Diploma"));
				System.out.println("Diploma : " + appt.getDiploma());
				appt.setYear(rs.getString("UA_Year"));
				appt.setMd5(rs.getString("ua_md5"));
				appt.setApprovedBy(rs.getString("ua_approvedby"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));
				appt.setResponsibility(rs
						.getString("appointment_responsibilities"));

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}
	
	// get all pending appointment
	public static ArrayList<Approval> getAllPendingAppointments(String roleType) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_AppointmentID, staffappointment.UA_EffectiveDate, staffappointment.UA_DateUpdated, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, ua_approvedby, appointment.appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status = -1";

		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, roleType);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setMd5(rs.getString("ua_md5"));
				appt.setApprovedBy(rs.getString("ua_approvedby"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));
				appt.setResponsibility(rs
						.getString("appointment_responsibilities"));
				appt.setApptLastUpdated(rs.getTimestamp("UA_DateUpdated"));
						//.getString("UA_DateUpdated"));
			

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}
	
	// get all old pending appointment
		public static ArrayList<Approval> getAllOldPendingAppointments(String roleType) {
			Connection con = DBConnection.connect(true);
			PreparedStatement stmt = null;
			ResultSet rs = null;
			String select = "SELECT staffappointment.UA_AppointmentID, staffappointment.UA_EffectiveDate, staffappointment.UA_DateUpdated, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, ua_approvedby, appointment.appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status = -1 AND datediff(now(), ua_dateupdated) > 7;";

			ArrayList<Approval> apptArr = new ArrayList<Approval>();
			Approval appt = null;

			try {
				stmt = con.prepareStatement(select);
				// stmt.setString(1, roleType);
				rs = stmt.executeQuery();
				while (rs.next()) {
					appt = new Approval();
					appt.setName(rs.getString("staff_name"));
					appt.setAppointment(rs.getString("appointment_name"));
					appt.setDescription(rs.getString("apmg_description"));
					appt.setMd5(rs.getString("ua_md5"));
					appt.setApprovedBy(rs.getString("ua_approvedby"));
					appt.setDate(rs.getString("UA_EffectiveDate"));
					appt.setAppointmentID(rs.getString("UA_AppointmentID"));
					appt.setResponsibility(rs
							.getString("appointment_responsibilities"));
					appt.setApptLastUpdated(rs.getTimestamp("UA_DateUpdated"));
							//.getString("UA_DateUpdated"));
				

					apptArr.add(appt);
				}
			} catch (Exception e) {
				e.printStackTrace();
				appt = null;
			} finally {
				try {
					if (rs != null)
						rs.close();
					if (stmt != null)
						stmt.close();
					if (con != null)
						con.close();
				} catch (SQLException e) {
				}
			}

			return apptArr;
		}
		
		// count all old pending appointment
				public static int countAllOldPendingAppointments() {
					Connection con = DBConnection.connect(true);
					PreparedStatement stmt = null;
					ResultSet rs = null;
					String select = "SELECT count(*) from staffappointment WHERE staffappointment.ua_status = -1 AND datediff(now(), ua_dateupdated) > 7;";

					int number = 0;

					try {
						stmt = con.prepareStatement(select);
						// stmt.setString(1, id);
						rs = stmt.executeQuery();
						while (rs.next()) {
							number = rs.getInt(1);
						}
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							if (rs != null)
								rs.close();
							if (stmt != null)
								stmt.close();
							if (con != null)
								con.close();
						} catch (SQLException e) {
						}
					}

					return number;
				}

	// Get login user's approved appointments
	public static ArrayList<Approval> getMyApprovedAppointments(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_AppointmentID, staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, ua_approvedby, appointment.appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status = 2 AND staffappointment.UA_LoginID = ?";

		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;

		try {

			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				
				appt.setMd5(rs.getString("ua_md5"));
				appt.setApprovedBy(rs.getString("ua_approvedby"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));
				appt.setResponsibility(rs
						.getString("appointment_responsibilities"));

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}

	// Get login user's pending appointments
	public static ArrayList<Approval> getMyPendingAppointments(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_AppointmentID, staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, appointment.appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status = -1 AND staffappointment.UA_LoginID = ?";

		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;

		try {

			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));
				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}

	public static Approval getAllApprovedAppointmentsByMD5(String md5) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_AppointmentID, staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointment.appointment_responsibilities, ua_md5, ua_approvedby FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status = 2 AND staffappointment.UA_MD5 = ?";

		Approval appt = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, md5);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("appointment_responsibilities"));
				appt.setMd5(rs.getString("ua_md5"));
				appt.setApprovedBy(rs.getString("ua_approvedby"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));

			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return appt;
	}

	public static ArrayList<Approval> getStaffOfAppointment2(String appointmentID) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE ua_appointmentid = ? and ua_status = 2";
		
		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;
		
		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, appointmentID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setResponsibility(rs
						.getString("appointment_responsibilities"));
				
				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		
		return apptArr;
	}
	public static ArrayList<Approval> getStaffOfAppointment(String appointmentID) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE ua_appointmentid = ?";

		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, appointmentID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setResponsibility(rs
						.getString("appointment_responsibilities"));

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}

	public static void updateStatus(Approval app) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "UPDATE staffappointment SET UA_Status = ?, UA_ApprovedBy = ? WHERE UA_MD5 = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, app.getStatus());
			stmt.setString(2, app.getApprovedBy());
			stmt.setString(3, app.getMd5());
			System.out.println(stmt.toString());
			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	// get name and id for printing
	public static Approval getNameAndId(String MD5) {

		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
	String select = "SELECT UA_LoginID,UA_AppointmentID,UA_Year,UA_Class,Dip_Name  FROM staffappointment s left JOIN diploma d ON s.UA_Diploma = d.Dip_Code WHERE UA_MD5 = ?";

		Approval app2 = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, MD5);
			rs = stmt.executeQuery();
			while (rs.next()) {
				app2 = new Approval();
				app2.setName(rs.getString("UA_LoginID"));
				app2.setAppointment(rs.getString("UA_AppointmentID"));
				app2.setDiploma(rs.getString("Dip_Name"));
				app2.setYear(rs.getString("UA_Year"));
				app2.setSaclass(rs.getString("UA_Class"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		return app2;
	}

	public static String getStaffName(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT Staff_Name FROM qmsdb.staff WHERE Staff_LoginID = ?";

		String name = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				name = rs.getString("Staff_Name");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		return name;
	}

	public static String getDate(String md5) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT UA_EffectiveDate FROM qmsdb.staffappointment WHERE UA_MD5 = ?";

		String name = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, md5);
			rs = stmt.executeQuery();
			while (rs.next()) {
				name = rs.getString("UA_EffectiveDate");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		return name;
	}

	// get number of records for pagination
	public int getNoOfRecords() {
		return noOfRecords;
	}

	public static ArrayList<Approval> getAllAppointmentsByAO(String roleType, String username) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, staffappointment.UA_AppointmentID FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status < 1 AND staffappointment.ua_loginid = " + username ;

		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, roleType);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setMd5(rs.getString("ua_md5"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}


	public static ArrayList<Approval> getAllApprovedAppointments(
			int appointmentID) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT staffappointment.UA_AppointmentID, UA_Year, UA_YearNo, UA_Diploma,staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, ua_approvedby, appointment.appointment_responsibilities FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status = 2 AND appointment.appointment_ID=?";
		// joe String select =
		// "SELECT staffappointment.UA_EffectiveDate, staff.staff_name, appointment.appointment_name, appointmentgroup.apmg_description, ua_md5, staffappointment.UA_AppointmentID FROM staffappointment INNER JOIN appointment ON appointment.appointment_ID = staffappointment.UA_AppointmentID INNER JOIN staff ON staff.staff_loginid = staffappointment.UA_LoginID INNER JOIN appointmentgroup ON appointmentgroup.apmg_id = staffappointment.ua_apptgrpid WHERE staffappointment.ua_status < 1";

		ArrayList<Approval> apptArr = new ArrayList<Approval>();
		Approval appt = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setInt(1, appointmentID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				appt = new Approval();
				appt.setName(rs.getString("staff_name"));
				appt.setAppointment(rs.getString("appointment_name"));
				appt.setDescription(rs.getString("apmg_description"));
				appt.setMd5(rs.getString("ua_md5"));
				appt.setDiploma(rs.getString("ua_diploma"));
				appt.setApprovedBy(rs.getString("ua_approvedby"));
				appt.setDate(rs.getString("UA_EffectiveDate"));
				appt.setAppointmentID(rs.getString("UA_AppointmentID"));
				appt.setResponsibility(rs
						.getString("appointment_responsibilities"));
				appt.setYear(rs.getString("UA_Year"));
				appt.setYearNo(rs.getString("UA_YearNo"));

				apptArr.add(appt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			appt = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return apptArr;
	}
	// end
}
